/**
 * Licensed to Jasig under one or more contributor license
 * agreements. See the NOTICE file distributed with this work
 * for additional information regarding copyright ownership.
 * Jasig licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a
 * copy of the License at:
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

package org.jasig.portal.groups;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jasig.portal.EntityIdentifier;
import org.jasig.portal.EntityTypes;
import org.jasig.portal.RDBMServices;
import org.jasig.portal.services.GroupService;
import org.jasig.portal.spring.locator.CounterStoreLocator;
import org.jasig.portal.utils.SqlTransaction;

/**
 * Store for <code>EntityGroupImpl</code>.
 * @author Dan Ellentuck
 * @version $Revision$
 */
public class RDBMEntityGroupStore implements IEntityGroupStore, IGroupConstants {
    private static final Log log = LogFactory.getLog(RDBMEntityGroupStore.class);
    private static RDBMEntityGroupStore singleton;

    // Constant SQL strings:
    private static String EQ = " = ";
    private static String QUOTE = "'";
    private static String EQUALS_PARAM = EQ + "?";

    // Constant strings for GROUP table:
    private static String GROUP_TABLE = "UP_GROUP";
    private static String GROUP_TABLE_ALIAS = "T1";
    private static String GROUP_TABLE_WITH_ALIAS = GROUP_TABLE + " " + GROUP_TABLE_ALIAS;
    private static String GROUP_ID_COLUMN = "GROUP_ID";
    private static String GROUP_CREATOR_COLUMN = "CREATOR_ID";
    private static String GROUP_TYPE_COLUMN = "ENTITY_TYPE_ID";
    private static String GROUP_NAME_COLUMN = "GROUP_NAME";
    private static String GROUP_DESCRIPTION_COLUMN = "DESCRIPTION";

    // SQL strings for GROUP crud:
    private static String allGroupColumns;
    private static String allGroupColumnsWithTableAlias;
    private static String countAMemberGroupSql;
    private static String countMemberGroupsNamedSql;
    private static String countAMemberEntitySql;
    private static String findContainingGroupsForEntitySql;
    private static String findContainingGroupsForGroupSql;
    private static String findGroupSql;
    private static String findGroupsByCreatorSql;
    private static String findMemberGroupKeysSql;
    private static String findMemberGroupsSql;
    private static String insertGroupSql;
    private static String updateGroupSql;

    // Constant strings for MEMBERS table:
    private static String MEMBER_TABLE = "UP_GROUP_MEMBERSHIP";
    private static String MEMBER_TABLE_ALIAS = "T2";
    private static String MEMBER_TABLE_WITH_ALIAS = MEMBER_TABLE + " " + MEMBER_TABLE_ALIAS;
    private static String MEMBER_GROUP_ID_COLUMN = "GROUP_ID";
    private static String MEMBER_MEMBER_SERVICE_COLUMN = "MEMBER_SERVICE";
    private static String MEMBER_MEMBER_KEY_COLUMN = "MEMBER_KEY";
    private static String MEMBER_IS_GROUP_COLUMN = "MEMBER_IS_GROUP";
    private static String MEMBER_IS_ENTITY = "F";
    private static String MEMBER_IS_GROUP = "T";
    private static String GROUP_NODE_SEPARATOR;

    // SQL strings for group MEMBERS crud:
    private static String allMemberColumns;
    private static String deleteMembersInGroupSql;
    private static String deleteMemberGroupSql;
    private static String deleteMemberEntitySql;
    private static String insertMemberSql;

    // SQL group search string
    private static String searchGroupsPartial = "SELECT "+GROUP_ID_COLUMN+" FROM "+GROUP_TABLE+" WHERE "+GROUP_TYPE_COLUMN+"=? AND UPPER("+GROUP_NAME_COLUMN+") LIKE UPPER(?)";
    private static String searchGroups = "SELECT "+GROUP_ID_COLUMN+" FROM "+GROUP_TABLE+" WHERE "+GROUP_TYPE_COLUMN+"=? AND UPPER("+GROUP_NAME_COLUMN+") = UPPER(?)";



/**
 * RDBMEntityGroupStore constructor.
 */
public RDBMEntityGroupStore()
{
    super();
    initialize();
}

/**
 * Get the node separator character from the GroupServiceConfiguration.
 * Default it to IGroupConstants.NODE_SEPARATOR.
 */
private void initialize() {
    String sep;
    try
        { sep = GroupServiceConfiguration.getConfiguration().getNodeSeparator(); }
    catch (Exception ex)
        { sep = NODE_SEPARATOR; }
    GROUP_NODE_SEPARATOR = sep;
    if (log.isDebugEnabled()) {
        log.debug("RDBMEntityGroupStore.initialize(): Node separator set to " + sep);
    }
}

/**
 * @param conn java.sql.Connection
 * @exception java.sql.SQLException
 */
protected static void commit(Connection conn) throws java.sql.SQLException
{
    SqlTransaction.commit(conn);
}

/**
 * Answers if <code>IGroupMember</code> member is a member of <code>group</code>.
 * @return boolean
 * @param group org.jasig.portal.groups.IEntityGroup
 * @param member org.jasig.portal.groups.IGroupMember
 */
public boolean contains(IEntityGroup group, IGroupMember member) throws GroupsException
{
    return ( member.isGroup() )
        ? containsGroup(group, (IEntityGroup)member)
        : containsEntity(group, member);
}

private boolean containsEntity(IEntityGroup group, IGroupMember member)
throws GroupsException
{
    String groupKey = group.getLocalKey();
    String memberKey = member.getKey();
    Connection conn = RDBMServices.getConnection();
    try
    {
        String sql = getCountAMemberEntitySql();
        PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
            ps.clearParameters();
            ps.setString(1, groupKey);
            ps.setString(2, memberKey);
            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.containsEntity(): " + ps +
                        " (" + groupKey + ", " + memberKey + ")");
            ResultSet rs = ps.executeQuery();
            try
            {
                return (rs.next()) &&
                       (rs.getInt(1) > 0);
            }
            finally
                { rs.close(); }
        }
        finally
            { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.containsEntity(): " + e);
        throw new GroupsException("Problem retrieving data from store: " + e);
    }
    finally
        { RDBMServices.releaseConnection(conn); }
}
private boolean containsGroup(IEntityGroup group, IEntityGroup member)
throws GroupsException
{
    String memberService = member.getServiceName().toString();
    String groupKey = group.getLocalKey();
    String memberKey = member.getLocalKey();
    Connection conn = RDBMServices.getConnection();
    try
    {
        String sql = getCountAMemberGroupSql();
        PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
            ps.clearParameters();
            ps.setString(1, groupKey);
            ps.setString(2, memberKey);
            ps.setString(3, memberService);
            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.containsGroup(): " + ps +
                        " (" + groupKey + ", " + memberKey + ", " + memberService + ")");
            ResultSet rs = ps.executeQuery();
            try
            {
                return (rs.next()) &&
                       (rs.getInt(1) > 0);
            }
            finally
                { rs.close(); }
        }
        finally
            { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.containsGroup(): " + e);
        throw new GroupsException("Problem retrieving data from store: " + e);
    }
    finally
        { RDBMServices.releaseConnection(conn); }
}
public boolean containsGroupNamed(IEntityGroup containingGroup, String memberName)
throws GroupsException
{
    String groupKey = containingGroup.getLocalKey();
    String service = containingGroup.getServiceName().toString();

    Connection conn = RDBMServices.getConnection();
    try
    {
        String sql = getCountMemberGroupsNamedSql();
        PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
            ps.clearParameters();
            ps.setString(1, groupKey);
            ps.setString(2, memberName);
            ps.setString(3, service);
            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.containsGroupNamed(): " + ps +
                        " (" + groupKey + ", " + memberName + ", " + service + ")");
            ResultSet rs = ps.executeQuery();
            try
            {
                return (rs.next()) &&
                       (rs.getInt(1) > 0);
            }
            finally
                { rs.close(); }
        }
        finally
            { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.containsGroup(): " + e);
        throw new GroupsException("Problem retrieving data from store: " + e);
    }
    finally
        { RDBMServices.releaseConnection(conn); }
}


/**
 * If this entity exists, delete it.
 * @param group org.jasig.portal.groups.IEntityGroup
 */
public void delete(IEntityGroup group) throws GroupsException
{
    if ( existsInDatabase(group) )
    {
        try
            { primDelete(group); }
        catch (SQLException sqle)
            { throw new GroupsException("Problem deleting " + group, sqle); }
    }
}

/**
 * Answer if the IEntityGroup entity exists in the database.
 * @return boolean
 * @param group IEntityGroup
 */
private boolean existsInDatabase(IEntityGroup group) throws GroupsException
{
    IEntityGroup ug = this.find(group.getLocalKey());
    return ug != null;
}

/**
 * Find and return an instance of the group.
 * @param groupID the group ID
 * @return org.jasig.portal.groups.IEntityGroup
 */
public IEntityGroup find(String groupID) throws GroupsException
{
    return primFind(groupID, false);
}

/**
 * Find the groups that this entity belongs to.
 * @param ent the entity in question
 * @return java.util.Iterator
 */
public java.util.Iterator findContainingGroups(IEntity ent) throws GroupsException
{
    String memberKey = ent.getKey();
    Integer type = EntityTypes.getEntityTypeID(ent.getLeafType());
    return findContainingGroupsForEntity(memberKey, type.intValue());
}

/**
 * Find the groups that this group belongs to.
 * @param group org.jasig.portal.groups.IEntityGroup
 * @return java.util.Iterator
 */
public java.util.Iterator findContainingGroups(IEntityGroup group) throws GroupsException
{
    String memberKey = group.getLocalKey();
    String serviceName = group.getServiceName().toString();
    Integer type = EntityTypes.getEntityTypeID(group.getLeafType());
    return findContainingGroupsForGroup(serviceName, memberKey, type.intValue());
}

/**
 * Find the groups that this group member belongs to.
 * @param gm the group member in question
 * @return java.util.Iterator
 */
public Iterator findContainingGroups(IGroupMember gm) throws GroupsException
{
    if ( gm.isGroup() )
    {
        IEntityGroup group = (IEntityGroup) gm;
        return findContainingGroups(group);
    }
    else
    {
        IEntity ent = (IEntity) gm;
        return findContainingGroups(ent);
    }
}

/**
 * Find the groups associated with this member key.
 * @param memberKey
 * @param type
 * @return java.util.Iterator
 */
private java.util.Iterator findContainingGroupsForEntity(String memberKey, int type)
throws GroupsException
{
    java.sql.Connection conn = null;
    Collection groups = new ArrayList();
    IEntityGroup eg = null;

    try
    {
            conn = RDBMServices.getConnection();
            String sql = getFindContainingGroupsForEntitySql();
            PreparedStatement ps = conn.prepareStatement(sql);
            try
            {
                    ps.setString(1, memberKey);
                    ps.setInt(2, type);
                    if (log.isDebugEnabled())
                        log.debug(
                                "RDBMEntityGroupStore.findContainingGroupsForEntity(): " + ps +
                                " (" + memberKey + ", " + type + ", memberIsGroup = F)");
                    java.sql.ResultSet rs = ps.executeQuery();
                    try
                    {
                            while (rs.next())
                            {
                                    eg = instanceFromResultSet(rs);
                                    groups.add(eg);
                            }
                    }
                    finally
                        { rs.close(); }
        }
            finally
                { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.findContainingGroupsForEntity(): " + e);
        throw new GroupsException("Problem retrieving containing groups: " + e);
    }

    finally
        { RDBMServices.releaseConnection(conn); }

    return groups.iterator();
}

/**
 * Find the groups associated with this member key.
 * @param serviceName
 * @param memberKey
 * @param type
 * @return java.util.Iterator
 */
private java.util.Iterator findContainingGroupsForGroup(String serviceName, String memberKey, int type)
throws GroupsException
{
    java.sql.Connection conn = null;
    Collection groups = new ArrayList();
    IEntityGroup eg = null;

    try
    {
            conn = RDBMServices.getConnection();
            String sql = getFindContainingGroupsForGroupSql();
            PreparedStatement ps = conn.prepareStatement(sql);
            try
            {
                    ps.setString(1, serviceName);
                    ps.setString(2, memberKey);
                    ps.setInt(3, type);
                    if (log.isDebugEnabled())
                        log.debug(
                                "RDBMEntityGroupStore.findContainingGroupsForGroup(): " + ps +
                                " (" + serviceName + ", " + memberKey + ", " + type + ", memberIsGroup = T)");
                    java.sql.ResultSet rs = ps.executeQuery();
                    try
                    {
                            while (rs.next())
                            {
                                    eg = instanceFromResultSet(rs);
                                    groups.add(eg);
                            }
                    }
                    finally
                        { rs.close(); }
        }
            finally
                { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.findContainingGroupsForGroup(): " + e);
        throw new GroupsException("Problem retrieving containing groups: " + e);
    }

    finally
        { RDBMServices.releaseConnection(conn); }

    return groups.iterator();
}

/**
 * Find the <code>IEntities</code> that are members of the <code>IEntityGroup</code>.
 * @param group the entity group in question
 * @return java.util.Iterator
 */
public Iterator findEntitiesForGroup(IEntityGroup group) throws GroupsException
{
    Collection entities = new ArrayList();
    Connection conn = null;
    String groupID = group.getLocalKey();
    Class cls = group.getLeafType();

    try
    {
        conn = RDBMServices.getConnection();
        Statement stmnt = conn.createStatement();
        try {

            String query = "SELECT " + MEMBER_MEMBER_KEY_COLUMN + " FROM " + MEMBER_TABLE +
                           " WHERE " + MEMBER_GROUP_ID_COLUMN + " = '" +  groupID +
                           "' AND "  + MEMBER_IS_GROUP_COLUMN + " = '" + MEMBER_IS_ENTITY + "'";

            ResultSet rs = stmnt.executeQuery(query);
            try {
                while (rs.next())
                {
                    String key = rs.getString(1);
                    IEntity e = newEntity(cls, key);
                    entities.add(e);
                }
            } finally {
                  rs.close();
              }
        } finally {
              stmnt.close();
          }
        }
    catch (SQLException sqle)
    {
        log.error("Problem retrieving Entities for Group: " + group, sqle);
        throw new GroupsException("Problem retrieving Entities for Group", sqle);
    }
    finally
        { RDBMServices.releaseConnection(conn); }

    return entities.iterator();
}

/**
 * Find the groups with this creatorID.
 * @param creatorID
 * @return java.util.Iterator
 */
public java.util.Iterator findGroupsByCreator(String creatorID) throws GroupsException
{
    java.sql.Connection conn = null;
    Collection groups = new ArrayList();
    IEntityGroup eg = null;

    try
    {
            conn = RDBMServices.getConnection();
            String sql = getFindGroupsByCreatorSql();
            PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
                ps.setString(1, creatorID);
                if (log.isDebugEnabled())
                    log.debug( "RDBMEntityGroupStore.findGroupsByCreator(): " + ps);
                ResultSet rs = ps.executeQuery();
                try
                {
                        while (rs.next())
                        {
                                eg = instanceFromResultSet(rs);
                                groups.add(eg);
                        }
                }
                finally
                    { rs.close(); }
                }
        finally
            { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.findGroupsByCreator(): " + e);
        throw new GroupsException("Problem retrieving groups: " + e);
    }

    finally
        { RDBMServices.releaseConnection(conn); }

    return groups.iterator();
}

/**
 * Find and return an instance of the group.
 * @param groupID the group ID
 * @return org.jasig.portal.groups.ILockableEntityGroup
 */
public ILockableEntityGroup findLockable(String groupID) throws GroupsException
{
    return (ILockableEntityGroup) primFind(groupID, true);
}

/**
 * Find the keys of groups that are members of group.
 * @param group the org.jasig.portal.groups.IEntityGroup
 * @return String[]
 */
public String[] findMemberGroupKeys(IEntityGroup group) throws GroupsException
{
    java.sql.Connection conn = null;
    Collection groupKeys = new ArrayList();
    String groupKey = null;

    try
    {
        conn = RDBMServices.getConnection();
        String sql = getFindMemberGroupKeysSql();
        PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
            ps.setString(1, group.getLocalKey());
            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.findMemberGroupKeys(): " + ps + " (" + group.getLocalKey() + ")");
            java.sql.ResultSet rs = ps.executeQuery();
            try
            {
                while (rs.next())
                {
                    groupKey = rs.getString(1) + GROUP_NODE_SEPARATOR + rs.getString(2);
                    groupKeys.add(groupKey);
                }
            }
            finally
                { rs.close(); }
        }
        finally
            { ps.close(); }
    }
    catch (Exception sqle)
        {
            log.error( "RDBMEntityGroupStore.findMemberGroupKeys(): " + sqle);
            throw new GroupsException("Problem retrieving member group keys: " + sqle);
        }
    finally
        { RDBMServices.releaseConnection(conn); }

    return (String[]) groupKeys.toArray(new String[groupKeys.size()]);
}
/**
 * Find the IUserGroups that are members of the group.
 * @param group org.jasig.portal.groups.IEntityGroup
 * @return java.util.Iterator
 */
public Iterator findMemberGroups(IEntityGroup group) throws GroupsException
{
    java.sql.Connection conn = null;
    Collection groups = new ArrayList();
    IEntityGroup eg = null;
    String serviceName = group.getServiceName().toString();
    String localKey = group.getLocalKey();

    try
    {
        conn = RDBMServices.getConnection();
        String sql = getFindMemberGroupsSql();
        PreparedStatement ps = conn.prepareStatement(sql);
        try
        {
            ps.setString(1, localKey);
            ps.setString(2, serviceName);
            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.findMemberGroups(): " + ps + 
                        " (" + localKey + ", " + serviceName + ")");
            java.sql.ResultSet rs = ps.executeQuery();
            try
            {
                while (rs.next())
                {
                    eg = instanceFromResultSet(rs);
                    groups.add(eg);
                }
            }
            finally
                { rs.close(); }
        }
        finally
            { ps.close(); }
    }
    catch (Exception sqle)
        {
            log.error( "RDBMEntityGroupStore.findMemberGroups(): " + sqle);
            throw new GroupsException("Problem retrieving member groups: " + sqle);
        }
    finally
        { RDBMServices.releaseConnection(conn); }

    return groups.iterator();
}
/**
 * @return java.lang.String
 */
private static java.lang.String getAllGroupColumns()
{

    if ( allGroupColumns == null )
    {
            StringBuffer buff = new StringBuffer(100);
            buff.append(GROUP_ID_COLUMN);
            buff.append(", ");
            buff.append(GROUP_CREATOR_COLUMN);
            buff.append(", ");
            buff.append(GROUP_TYPE_COLUMN);
            buff.append(", ");
            buff.append(GROUP_NAME_COLUMN);
            buff.append(", ");
            buff.append(GROUP_DESCRIPTION_COLUMN);

            allGroupColumns = buff.toString();
    }
    return allGroupColumns;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getAllGroupColumnsWithTableAlias()
{

    if ( allGroupColumnsWithTableAlias == null )
    {
            StringBuffer buff = new StringBuffer(100);
            buff.append(groupAlias(GROUP_ID_COLUMN));
            buff.append(", ");
            buff.append(groupAlias(GROUP_CREATOR_COLUMN));
            buff.append(", ");
            buff.append(groupAlias(GROUP_TYPE_COLUMN));
            buff.append(", ");
            buff.append(groupAlias(GROUP_NAME_COLUMN));
            buff.append(", ");
            buff.append(groupAlias(GROUP_DESCRIPTION_COLUMN));

            allGroupColumnsWithTableAlias = buff.toString();
        }
    return allGroupColumnsWithTableAlias;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getAllMemberColumns()
{
    if ( allMemberColumns == null )
    {
            StringBuffer buff = new StringBuffer(100);

            buff.append(MEMBER_GROUP_ID_COLUMN);
            buff.append(", ");
            buff.append(MEMBER_MEMBER_SERVICE_COLUMN);
            buff.append(", ");
            buff.append(MEMBER_MEMBER_KEY_COLUMN);
            buff.append(", ");
            buff.append(MEMBER_IS_GROUP_COLUMN);

            allMemberColumns = buff.toString();
    }
    return allMemberColumns;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getCountAMemberEntitySql() {
    if ( countAMemberEntitySql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("SELECT COUNT(*) FROM " + MEMBER_TABLE);
        buff.append(" WHERE " + MEMBER_GROUP_ID_COLUMN + EQUALS_PARAM);
        buff.append(" AND " + MEMBER_MEMBER_KEY_COLUMN + EQUALS_PARAM);
        buff.append(" AND " + MEMBER_IS_GROUP_COLUMN + EQ + sqlQuote(MEMBER_IS_ENTITY));
        countAMemberEntitySql = buff.toString();
    }
    return countAMemberEntitySql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getCountAMemberGroupSql() {
    if ( countAMemberGroupSql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("SELECT COUNT(*) FROM " + MEMBER_TABLE);
        buff.append(" WHERE " + MEMBER_GROUP_ID_COLUMN + EQUALS_PARAM);
        buff.append(" AND " + MEMBER_MEMBER_KEY_COLUMN + EQUALS_PARAM);
        buff.append(" AND " + MEMBER_MEMBER_SERVICE_COLUMN + EQUALS_PARAM);
        buff.append(" AND " + MEMBER_IS_GROUP_COLUMN + EQ + sqlQuote(MEMBER_IS_GROUP));
        countAMemberGroupSql = buff.toString();
    }
    return countAMemberGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getCountMemberGroupsNamedSql() {
    if ( countMemberGroupsNamedSql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("SELECT COUNT (*) FROM ");
        buff.append(GROUP_TABLE_WITH_ALIAS + ", " + MEMBER_TABLE_WITH_ALIAS);
        buff.append(" WHERE " + groupAlias(GROUP_ID_COLUMN) + EQ );
        buff.append(memberAlias(MEMBER_MEMBER_KEY_COLUMN));
        buff.append(" AND " + memberAlias(MEMBER_GROUP_ID_COLUMN) + EQUALS_PARAM);
        buff.append(" AND " + groupAlias(GROUP_NAME_COLUMN) + EQUALS_PARAM);
        buff.append(" AND " + memberAlias(MEMBER_MEMBER_SERVICE_COLUMN) + EQUALS_PARAM);
        countMemberGroupsNamedSql = buff.toString();
    }
    return countMemberGroupsNamedSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getDeleteGroupSql(IEntityGroup group)
{
    StringBuffer buff = new StringBuffer(100);
    buff.append("DELETE FROM ");
    buff.append(GROUP_TABLE);
    buff.append(" WHERE ");
    buff.append(GROUP_ID_COLUMN + EQ + sqlQuote(group.getLocalKey()));
    return buff.toString();
}
/**
 * @return java.lang.String
 */
private static java.lang.String getDeleteMemberEntitySql()
{
    if ( deleteMemberEntitySql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("DELETE FROM ");
        buff.append(MEMBER_TABLE);
        buff.append(" WHERE ");
        buff.append(MEMBER_GROUP_ID_COLUMN + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(MEMBER_MEMBER_KEY_COLUMN + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(MEMBER_IS_GROUP_COLUMN + EQ + sqlQuote(MEMBER_IS_ENTITY));

        deleteMemberEntitySql = buff.toString();
    }
    return deleteMemberEntitySql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getDeleteMemberGroupSql()
{
    if ( deleteMemberGroupSql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("DELETE FROM ");
        buff.append(MEMBER_TABLE);
        buff.append(" WHERE ");
        buff.append(MEMBER_GROUP_ID_COLUMN + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(MEMBER_MEMBER_SERVICE_COLUMN + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(MEMBER_MEMBER_KEY_COLUMN + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(MEMBER_IS_GROUP_COLUMN + EQ + sqlQuote(MEMBER_IS_GROUP));
        deleteMemberGroupSql = buff.toString();
    }
    return deleteMemberGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getDeleteMembersInGroupSql()
{
    if ( deleteMembersInGroupSql == null )
    {
        StringBuffer buff = new StringBuffer(100);
        buff.append("DELETE FROM ");
        buff.append(MEMBER_TABLE);
        buff.append(" WHERE ");
        buff.append(GROUP_ID_COLUMN + EQ);

        deleteMembersInGroupSql = buff.toString();
    }
    return deleteMembersInGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getDeleteMembersInGroupSql(IEntityGroup group)
{
    return getDeleteMembersInGroupSql() + sqlQuote(group.getLocalKey());
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindContainingGroupsForEntitySql()
{
    if ( findContainingGroupsForEntitySql == null)
    {
        StringBuffer buff = new StringBuffer(500);
        buff.append("SELECT ");
        buff.append(getAllGroupColumnsWithTableAlias());
        buff.append(" FROM " + GROUP_TABLE_WITH_ALIAS + ", " + MEMBER_TABLE_WITH_ALIAS);
        buff.append(" WHERE ");
        buff.append(groupAlias(GROUP_ID_COLUMN) + EQ);
        buff.append(memberAlias(MEMBER_GROUP_ID_COLUMN));
        buff.append(" AND ");
        buff.append(memberAlias(MEMBER_MEMBER_KEY_COLUMN) + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(groupAlias(GROUP_TYPE_COLUMN) + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(memberAlias(MEMBER_IS_GROUP_COLUMN) + EQ + sqlQuote(MEMBER_IS_ENTITY));

       findContainingGroupsForEntitySql = buff.toString();
    }
    return findContainingGroupsForEntitySql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindContainingGroupsForGroupSql()
{
    if ( findContainingGroupsForGroupSql == null)
    {
        StringBuffer buff = new StringBuffer(500);
        buff.append("SELECT ");
        buff.append(getAllGroupColumnsWithTableAlias());
        buff.append(" FROM ");
        buff.append(GROUP_TABLE_WITH_ALIAS);
        buff.append(", ");
        buff.append(MEMBER_TABLE_WITH_ALIAS);
        buff.append(" WHERE ");
        buff.append(groupAlias(GROUP_ID_COLUMN) + EQ);
        buff.append(memberAlias(MEMBER_GROUP_ID_COLUMN));
        buff.append(" AND ");
        buff.append(memberAlias(MEMBER_MEMBER_SERVICE_COLUMN) + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(memberAlias(MEMBER_MEMBER_KEY_COLUMN) + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(groupAlias(GROUP_TYPE_COLUMN) + EQUALS_PARAM);
        buff.append(" AND ");
        buff.append(memberAlias(MEMBER_IS_GROUP_COLUMN) + EQ + sqlQuote(MEMBER_IS_GROUP));

       findContainingGroupsForGroupSql = buff.toString();
    }
    return findContainingGroupsForGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindGroupsByCreatorSql()
{
    if ( findGroupsByCreatorSql == null )
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("SELECT ");
            buff.append(getAllGroupColumns());
            buff.append(" FROM ");
            buff.append(GROUP_TABLE);
            buff.append(" WHERE ");
            buff.append(GROUP_CREATOR_COLUMN + EQUALS_PARAM);

            findGroupsByCreatorSql = buff.toString();
    }
    return findGroupsByCreatorSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindGroupSql()
{

    if ( findGroupSql == null )
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("SELECT ");
            buff.append(getAllGroupColumns());
            buff.append(" FROM ");
            buff.append(GROUP_TABLE);
            buff.append(" WHERE ");
            buff.append(GROUP_ID_COLUMN + EQUALS_PARAM);

            findGroupSql = buff.toString();
    }
    return findGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindMemberGroupKeysSql()
{
    if (findMemberGroupKeysSql == null)
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("SELECT ");
            buff.append(MEMBER_MEMBER_SERVICE_COLUMN + ", " + MEMBER_MEMBER_KEY_COLUMN);
            buff.append(" FROM ");
            buff.append(MEMBER_TABLE);
            buff.append(" WHERE ");
            buff.append(MEMBER_GROUP_ID_COLUMN + EQUALS_PARAM);
            buff.append(" AND ");
            buff.append(MEMBER_IS_GROUP_COLUMN + EQ);
            buff.append(sqlQuote(MEMBER_IS_GROUP));

            findMemberGroupKeysSql = buff.toString();
    }

    return findMemberGroupKeysSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getFindMemberGroupsSql()
{
    if (findMemberGroupsSql == null)
    {
            StringBuffer buff = new StringBuffer(500);
            buff.append("SELECT ");
            buff.append(getAllGroupColumnsWithTableAlias());
            buff.append(" FROM ");
            buff.append(GROUP_TABLE + " " + GROUP_TABLE_ALIAS);
            buff.append(", ");
            buff.append(MEMBER_TABLE + " " + MEMBER_TABLE_ALIAS);
            buff.append(" WHERE ");
            buff.append(groupAlias(GROUP_ID_COLUMN) + EQ);
            buff.append(memberAlias(MEMBER_MEMBER_KEY_COLUMN));
            buff.append(" AND ");
            buff.append(memberAlias(MEMBER_IS_GROUP_COLUMN) + EQ);
            buff.append(sqlQuote(MEMBER_IS_GROUP));
            buff.append(" AND ");
            buff.append(memberAlias(MEMBER_GROUP_ID_COLUMN) + EQUALS_PARAM);
            buff.append(" AND ");
            buff.append(memberAlias(MEMBER_MEMBER_SERVICE_COLUMN) + EQUALS_PARAM);

            findMemberGroupsSql = buff.toString();
        }

    return findMemberGroupsSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getInsertGroupSql()
{
    if ( insertGroupSql == null )
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("INSERT INTO ");
            buff.append(GROUP_TABLE);
            buff.append(" (");
            buff.append(getAllGroupColumns());
            buff.append(") VALUES (?, ?, ?, ?, ?)");

            insertGroupSql = buff.toString();
    }
    return insertGroupSql;
}
/**
 * @return java.lang.String
 */
private static java.lang.String getInsertMemberSql()
{
    if ( insertMemberSql == null )
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("INSERT INTO ");
            buff.append(MEMBER_TABLE);
            buff.append(" (");
            buff.append(getAllMemberColumns());
            buff.append(") VALUES (?, ?, ?, ? )");

            insertMemberSql = buff.toString();
    }
    return insertMemberSql;
}
/**
 * @return java.lang.String
 * @exception java.lang.Exception
 */
private String getNextKey() throws java.lang.Exception
{
    return Integer.toString(CounterStoreLocator.getCounterStore().getNextId(GROUP_TABLE));
}
/**
 * @return java.lang.String
 */
private static java.lang.String getUpdateGroupSql()
{
    if ( updateGroupSql == null )
    {
            StringBuffer buff = new StringBuffer(200);
            buff.append("UPDATE ");
            buff.append(GROUP_TABLE);
            buff.append(" SET ");
            buff.append(GROUP_CREATOR_COLUMN + EQUALS_PARAM);
            buff.append(", ");
            buff.append(GROUP_TYPE_COLUMN + EQUALS_PARAM);
            buff.append(", ");
            buff.append(GROUP_NAME_COLUMN + EQUALS_PARAM);
            buff.append(", ");
            buff.append(GROUP_DESCRIPTION_COLUMN + EQUALS_PARAM);
            buff.append(" WHERE ");
            buff.append(GROUP_ID_COLUMN + EQUALS_PARAM);

            updateGroupSql = buff.toString();
    }
    return updateGroupSql;
}

/**
 * Find and return an instance of the group.
 * @param rs the SQL result set
 * @return org.jasig.portal.groups.IEntityGroup
 */
private IEntityGroup instanceFromResultSet(java.sql.ResultSet rs)
throws  SQLException,
        GroupsException
{
    IEntityGroup eg = null;

    String key = rs.getString(1);
    String creatorID = rs.getString(2);
    Integer entityTypeID = new Integer(rs.getInt(3));
    Class entityType = EntityTypes.getEntityType(entityTypeID);
    String groupName = rs.getString(4);
    String description = rs.getString(5);

    if ( key != null )
        { eg = newInstance(key, entityType, creatorID, groupName, description); }

    return eg;
}

/**
 * Find and return an instance of the group.
 * @param rs the SQL result set
 * @return org.jasig.portal.groups.ILockableEntityGroup
 */
private ILockableEntityGroup lockableInstanceFromResultSet(java.sql.ResultSet rs)
throws  SQLException,
        GroupsException
{
    ILockableEntityGroup eg = null;

    String key = rs.getString(1);
    String creatorID = rs.getString(2);
    Integer entityTypeID = new Integer(rs.getInt(3));
    Class entityType = EntityTypes.getEntityType(entityTypeID);
    String groupName = rs.getString(4);
    String description = rs.getString(5);

    if ( key != null )
        { eg = newLockableInstance(key, entityType, creatorID, groupName, description); }

    return eg;
}
/**
 *
 */
protected static void logNoTransactionWarning()
{
    if (log.isWarnEnabled()) {
        String msg = "You are running the portal on a database that does not support transactions.  " +
        "This is not a supported production environment for uPortal.  " +
        "Sooner or later, your database will become corrupt.";
        
        log.warn(msg);
    }
}
/**
 * @return org.jasig.portal.groups.IEntity
 */
public IEntity newEntity(Class type, String key) throws GroupsException
{
    if ( EntityTypes.getEntityTypeID(type) == null )
        { throw new GroupsException("Invalid group type: " + type); }
    return GroupService.getEntity(key, type);
}
/**
 * @return org.jasig.portal.groups.IEntityGroup
 */
public IEntityGroup newInstance(Class type) throws GroupsException
{
    if ( EntityTypes.getEntityTypeID(type) == null )
        { throw new GroupsException("Invalid group type: " + type); }
    try
        { return new EntityGroupImpl(getNextKey(), type); }
    catch ( Exception ex )
        { throw new GroupsException("Could not create new group", ex); }
}
/**
 * @return org.jasig.portal.groups.IEntityGroup
 */
private IEntityGroup newInstance
    (String newKey,
    Class newType,
    String newCreatorID,
    String newName,
    String newDescription)
    throws GroupsException
{
    EntityGroupImpl egi = new EntityGroupImpl(newKey, newType);
    egi.setCreatorID(newCreatorID);
    egi.primSetName(newName);
    egi.setDescription(newDescription);
    return egi;
}
/**
 * @return org.jasig.portal.groups.ILockableEntityGroup
 */
private ILockableEntityGroup newLockableInstance
    (String newKey,
    Class newType,
    String newCreatorID,
    String newName,
    String newDescription)
    throws GroupsException
{
    LockableEntityGroupImpl group = new LockableEntityGroupImpl(newKey, newType);
    group.setCreatorID(newCreatorID);
    group.primSetName(newName);
    group.setDescription(newDescription);
    return group;
}
/**
 * @return java.lang.String
 */
private static java.lang.String groupAlias(String column)
{
    return GROUP_TABLE_ALIAS + "." + column;
}

/**
 * @return java.lang.String
 */
private static java.lang.String memberAlias(String column)
{
    return MEMBER_TABLE_ALIAS + "." + column;
}

/**
 * Insert the entity into the database.
 * @param group org.jasig.portal.groups.IEntityGroup
 * @param conn the database connection
 */
private void primAdd(IEntityGroup group, Connection conn) throws SQLException, GroupsException
{
    try
    {
        PreparedStatement ps =
            conn.prepareStatement(getInsertGroupSql());
       try
        {
            Integer typeID = EntityTypes.getEntityTypeID(group.getLeafType());
            ps.setString(1, group.getLocalKey());
            ps.setString(2, group.getCreatorID());
            ps.setInt   (3, typeID.intValue());
            ps.setString(4, group.getName());
            ps.setString(5, group.getDescription());

            if (log.isDebugEnabled())
                log.debug("RDBMEntityGroupStore.primAdd(): " +
                        ps + "(" + group.getLocalKey() + ", " + group.getCreatorID() + ", " +
                        typeID + ", " + group.getName() + ", " +
                        group.getDescription() + ")" );

            int rc = ps.executeUpdate();

            if ( rc != 1 )
            {
                String errString = "Problem adding " + group;
                log.error( errString);
                throw new GroupsException(errString);
            }
        }
        finally
            { ps.close(); }
    }
    catch (java.sql.SQLException sqle)
    {
        log.error("Error inserting an entity into the database. Group:" + group, sqle);
        throw sqle;
    }
}
/**
 * Delete this entity from the database after first deleting
 * its memberships.
 * Exception java.sql.SQLException - if we catch a SQLException,
 * we rollback and re-throw it.
 * @param group org.jasig.portal.groups.IEntityGroup
 */
private void primDelete(IEntityGroup group) throws SQLException
{
    java.sql.Connection conn = null;
    String deleteGroupSql = getDeleteGroupSql(group);
    String deleteMembershipSql = getDeleteMembersInGroupSql(group);

    try
    {
        conn = RDBMServices.getConnection();
        Statement stmnt = conn.createStatement();
        setAutoCommit(conn, false);

        try
        {
            if (log.isDebugEnabled())
                log.debug(
                  "RDBMEntityGroupStore.primDelete(): " + deleteMembershipSql);
                
            stmnt.executeUpdate(deleteMembershipSql);

            if (log.isDebugEnabled())
                log.debug(
                  "RDBMEntityGroupStore.primDelete(): " + deleteGroupSql);
            stmnt.executeUpdate(deleteGroupSql);
        }
        finally
            { stmnt.close(); }
        commit(conn);

    }
    catch (SQLException sqle)
    {
        rollback(conn);
        throw sqle;
    }
    finally
    {
        try
            { setAutoCommit(conn, true); }
        finally
            { RDBMServices.releaseConnection(conn); }
    }
}
/**
 * Find and return an instance of the group.
 * @param groupID the group ID
 * @param lockable boolean
 * @return org.jasig.portal.groups.IEntityGroup
 */
private IEntityGroup primFind(String groupID, boolean lockable) throws GroupsException
{
    IEntityGroup eg = null;
    java.sql.Connection conn = null;
    try
    {
            conn = RDBMServices.getConnection();
            String sql = getFindGroupSql();
            PreparedStatement ps = conn.prepareStatement(sql);
            try
            {
                    ps.setString(1, groupID);
                    if (log.isDebugEnabled())
                        log.debug(
                                "RDBMEntityGroupStore.find(): " + ps + " (" + groupID + ")");
                    java.sql.ResultSet rs = ps.executeQuery();
                    try
                    {
                            while (rs.next())
                            {
                                eg = (lockable)
                                    ? lockableInstanceFromResultSet(rs)
                                    : instanceFromResultSet(rs);
                            }
                    }
                    finally
                        { rs.close(); }
            }
            finally
                { ps.close(); }
    }
    catch (Exception e)
    {
        log.error( "RDBMEntityGroupStore.find(): ", e);
        throw new GroupsException("Error retrieving " + groupID + ": ", e);
    }
    finally
        { RDBMServices.releaseConnection(conn); }

    return eg;
}

/**
 * Update the entity in the database.
 * @param group org.jasig.portal.groups.IEntityGroup
 * @param conn the database connection
 */
private void primUpdate(IEntityGroup group, Connection conn) throws SQLException, GroupsException
{
    try
    {
        PreparedStatement ps =
            conn.prepareStatement(getUpdateGroupSql());

        try
        {
            Integer typeID = EntityTypes.getEntityTypeID(group.getLeafType());

            ps.setString(1, group.getCreatorID());
            ps.setInt(2, typeID.intValue());
            ps.setString(3, group.getName());
            ps.setString(4, group.getDescription());
            ps.setString(5, group.getLocalKey());

            if (log.isDebugEnabled())
                log.debug(
                        "RDBMEntityGroupStore.primUpdate(): " + ps + "(" +
                        group.getCreatorID() + ", " + typeID + ", " + group.getName() +
                        ", " + group.getDescription() + ", " + group.getLocalKey() +  ")" );

            int rc = ps.executeUpdate();

            if ( rc != 1 )
            {
                String errString = "Problem updating " + group;
                log.error( errString);
                throw new GroupsException(errString);
            }
        }
        finally
            { ps.close(); }
    }
    catch (java.sql.SQLException sqle)
    {
        log.error("Error updating entity in database. Group: " + group, sqle);
        throw sqle;
    }
}

/**
 * Insert and delete group membership rows.  The transaction is maintained by
 * the caller.
 * @param egi org.jasig.portal.groups.EntityGroupImpl
 * @param conn the database connection
 */
private void primUpdateMembers(EntityGroupImpl egi, Connection conn) throws java.sql.SQLException
{
    String groupKey = egi.getLocalKey();
    String memberKey, isGroup, serviceName = null;
    try
    {
        if ( egi.hasDeletes() )
        {
            List deletedGroups = new ArrayList();
            List deletedEntities = new ArrayList();
            Iterator deletes = egi.getRemovedMembers().values().iterator();
            while ( deletes.hasNext() )
            {
                IGroupMember gm = (IGroupMember) deletes.next();
                if ( gm.isGroup() )
                    { deletedGroups.add(gm);}
                else
                    { deletedEntities.add(gm);}
            }

            if ( ! deletedGroups.isEmpty() )
            {
                PreparedStatement psDeleteMemberGroup =
                    conn.prepareStatement(getDeleteMemberGroupSql());

                try
                {
                    for ( Iterator groups = deletedGroups.iterator(); groups.hasNext(); )
                    {
                        IEntityGroup removedGroup = (IEntityGroup) groups.next();
                        memberKey = removedGroup.getLocalKey();
                        isGroup = MEMBER_IS_GROUP;
                        serviceName = removedGroup.getServiceName().toString();

                        psDeleteMemberGroup.setString(1, groupKey);
                        psDeleteMemberGroup.setString(2, serviceName);
                        psDeleteMemberGroup.setString(3, memberKey);

                        if (log.isDebugEnabled())
                            log.debug(
                                    "RDBMEntityGroupStore.primUpdateMembers(): " + psDeleteMemberGroup +
                                    "(" + groupKey + ", " + serviceName + ", " + memberKey + ", isGroup = T)" );

                        psDeleteMemberGroup.executeUpdate();
                    } // for
                }     // try
                finally
                    { psDeleteMemberGroup.close(); }
            }         // if ( ! deletedGroups.isEmpty() )

            if ( ! deletedEntities.isEmpty() )
            {
                PreparedStatement psDeleteMemberEntity =
                    conn.prepareStatement(getDeleteMemberEntitySql());

                try
                {
                    for ( Iterator entities = deletedEntities.iterator(); entities.hasNext(); )
                    {
                        IGroupMember removedEntity = (IGroupMember) entities.next();
                        memberKey = removedEntity.getUnderlyingEntityIdentifier().getKey();
                        isGroup = MEMBER_IS_ENTITY;

                        psDeleteMemberEntity.setString(1, groupKey);
                        psDeleteMemberEntity.setString(2, memberKey);

                        if (log.isDebugEnabled())
                            log.debug(
                                    "RDBMEntityGroupStore.primUpdateMembers(): " + psDeleteMemberEntity +
                                    "(" + groupKey + ", " + memberKey + ", " + "isGroup = F)" );

                        psDeleteMemberEntity.executeUpdate();
                    }  // for
                }      // try
                finally
                    { psDeleteMemberEntity.close(); }
            }          //  if ( ! deletedEntities.isEmpty() )

        }

        if ( egi.hasAdds() )
        {
            PreparedStatement psAdd =
                conn.prepareStatement(getInsertMemberSql());

            try
            {
                Iterator adds = egi.getAddedMembers().values().iterator();
                while ( adds.hasNext() )
                {
                    IGroupMember addedGM = (IGroupMember) adds.next();
                    memberKey = addedGM.getKey();
                    if ( addedGM.isGroup() )
                    {
                        IEntityGroup addedGroup = (IEntityGroup) addedGM;
                        isGroup = MEMBER_IS_GROUP;
                        serviceName = addedGroup.getServiceName().toString();
                        memberKey = addedGroup.getLocalKey();
                    }
                    else
                    {
                        isGroup = MEMBER_IS_ENTITY;
                        serviceName = egi.getServiceName().toString();
                        memberKey = addedGM.getUnderlyingEntityIdentifier().getKey();
                    }

                    psAdd.setString(1, groupKey);
                    psAdd.setString(2, serviceName);
                    psAdd.setString(3, memberKey);
                    psAdd.setString(4, isGroup);

                    if (log.isDebugEnabled())
                        log.debug(
                                "RDBMEntityGroupStore.primUpdateMembers(): " + psAdd +
                                "(" + groupKey + ", " + memberKey + ", " + isGroup + ")" );

                    psAdd.executeUpdate();
                }
            }
            finally
                { psAdd.close(); }
        }

    }
    catch (SQLException sqle)
    {
        log.error("Error inserting/deleting membership rows.", sqle);
        throw sqle;
    }
}

/**
 * @param conn java.sql.Connection
 * @exception java.sql.SQLException
 */
protected static void rollback(Connection conn) throws java.sql.SQLException
{
    SqlTransaction.rollback(conn);
}

  public EntityIdentifier[] searchForGroups(String query, int method, Class leaftype) throws GroupsException {
    EntityIdentifier[] r = new EntityIdentifier[0];
    ArrayList ar = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    int type = EntityTypes.getEntityTypeID(leaftype).intValue();
    //System.out.println("Checking out groups of leaftype "+leaftype.getName()+" or "+type);

        try {
            conn = RDBMServices.getConnection();
           
           	switch(method){
           	case IS:
           		ps = conn.prepareStatement(RDBMEntityGroupStore.searchGroups);
           		break;
           	case STARTS_WITH:
           		query = query+"%";
           		ps = conn.prepareStatement(RDBMEntityGroupStore.searchGroupsPartial);
           		break;
           	case ENDS_WITH:
           		query = "%"+query;
           		ps = conn.prepareStatement(RDBMEntityGroupStore.searchGroupsPartial);
           		break;
           	case CONTAINS:
           		query = "%"+query+"%";
           		ps = conn.prepareStatement(RDBMEntityGroupStore.searchGroupsPartial);
           		break;
           	default:
           		throw new GroupsException("Unknown search type");
           	}
           	try {
            	ps.clearParameters();
            	ps.setInt(1,type);
            	ps.setString(2,query);
            	ResultSet rs = ps.executeQuery();
            	try {
            		//System.out.println(ps.toString());
            		while (rs.next()){
            			//System.out.println("result");
            			ar.add(new EntityIdentifier(rs.getString(1), org.jasig.portal.EntityTypes.GROUP_ENTITY_TYPE));
            		}
            	} finally {
            		close(rs);
            	}
            } finally {
            	close(ps);
            }
        } catch (Exception e) {
            log.error("RDBMChannelDefSearcher.searchForEntities(): " + ps, e);
        } finally {
            RDBMServices.releaseConnection(conn);
        }
      return (EntityIdentifier[]) ar.toArray(r);
  }

/**
 * @param conn java.sql.Connection
 * @param newValue boolean
 * @exception java.sql.SQLException The exception description.
 */
protected static void setAutoCommit(Connection conn, boolean newValue) throws java.sql.SQLException
{
    SqlTransaction.setAutoCommit(conn, newValue);
}

/**
 * @return org.jasig.portal.groups.RDBMEntityGroupStore
 */
public static synchronized RDBMEntityGroupStore singleton()
throws GroupsException
{
    if ( singleton == null )
        { singleton = new RDBMEntityGroupStore(); }
    return singleton;
}
/**
 * @return java.lang.String
 */
private static java.lang.String sqlQuote(Object o)
{
    return QUOTE + o + QUOTE;
}
/**
 * Commit this entity AND ITS MEMBERSHIPS to the underlying store.
 * @param group org.jasig.portal.groups.IEntityGroup
 */
public void update(IEntityGroup group) throws GroupsException
{
    Connection conn = null;
    boolean exists = existsInDatabase(group);
    try
    {
        conn = RDBMServices.getConnection();
        setAutoCommit(conn, false);

        try
        {
            if ( exists )
                { primUpdate(group, conn); }
            else
                { primAdd(group, conn); }
            primUpdateMembers((EntityGroupImpl)group, conn);
            commit(conn);
        }

        catch (Exception ex)
        {
            rollback(conn);
            throw new GroupsException("Problem updating " + this + ex);
        }
    }

    catch ( SQLException sqlex )
        { throw new GroupsException(sqlex); }

    finally
    {
        try { setAutoCommit(conn, true); }
        catch (SQLException sqle)
            { throw new GroupsException(sqle); }
        finally { RDBMServices.releaseConnection(conn); }
    }
}

/**
 * Insert and delete group membership rows inside a transaction.
 * @param eg org.jasig.portal.groups.IEntityGroup
 */
public void updateMembers(IEntityGroup eg) throws GroupsException
{
    Connection conn = null;
    EntityGroupImpl egi = (EntityGroupImpl) eg;
    if ( egi.isDirty() )
    try
    {
        conn = RDBMServices.getConnection();
        setAutoCommit(conn, false);

        try
        {
            primUpdateMembers(egi, conn);
            commit(conn);
        }
        catch ( SQLException sqle )
        {
            rollback(conn);
            throw new GroupsException("Problem updating memberships for " + egi, sqle);
        }
    }

    catch ( SQLException sqlex )
        { throw new GroupsException(sqlex); }

    finally
    {
        try { setAutoCommit(conn, true); }
        catch (SQLException sqle)
            { throw new GroupsException(sqle); }
        finally { RDBMServices.releaseConnection(conn); }
    }
}

private static final void close(final Statement statement) {
	try {
		statement.close();
	} catch (SQLException e) {
		log.warn("problem closing statement", e);
	}
}

private static final void close(final ResultSet resultset) {
	try {
		resultset.close();
	} catch (SQLException e) {
		log.warn("problem closing resultset", e);
	}
}
}
